﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using System.Data;
using System.Data.SqlClient;
using DBUnitility;
namespace DAL
{
    public class LandNodeService
    {
        #region 查询
        /// <summary>
        /// 按工程文件ID读取所有地标信息
        /// </summary>
        /// <param name="id">工程文件ID</param>
        /// <returns></returns>
        public List<LandNode> GetAllLandNodesById(string id)
        {
            string sql = "Select LandId,LandNum,NodeName,NodeTypeId,X,Y,ID from LandInfo where ID = " + id;
            List<LandNode> list = new List<LandNode>();
            try
            {
                SqlDataReader objReader = SQLHelper.GetReader(sql);
                while (objReader.Read())
                {
                    list.Add(new LandNode() 
                    {
                        LandId = Convert.ToInt32(objReader["LandId"]),
                        LandNum = Convert.ToInt32(objReader["LandNum"]),
                        NodeName = objReader["NodeName"].ToString(),
                        NodeTypeId = Convert.ToInt32(objReader["NodeTypeId"]),
                        X = Convert.ToInt32(objReader["X"]),
                        Y = Convert.ToInt32(objReader["Y"]),
                        ID = Convert.ToInt32(objReader["ID"])
                    });
                }
                objReader.Close();
                return list;
            }
            catch (Exception ex)
            {
                
                throw new Exception("按ID读取地标信息失败！" + ex.Message);
            }           
        }
        /// <summary>
        /// 根据地标号和ID号获取地标信息
        /// </summary>
        /// <param name="landNum">地标号</param>
        /// <param name="id">工程ID</param>
        /// <returns></returns>
        public LandNode GetLandNode(string landNum, string id)
        {
            string sql = "Select LandId,LandNum,NodeName,NodeTypeId,X,Y,ID from LandInfo where LandNum = {0} and ID = {1} ";
            sql = string.Format(sql,landNum,id);
            LandNode objLandNode = null;
            try
            {
                SqlDataReader objReader = SQLHelper.GetReader(sql);
                if (objReader.Read())
                {
                    objLandNode = new LandNode() 
                    {
                        LandId = Convert.ToInt32(objReader["LandId"]),
                        LandNum = Convert.ToInt32(objReader["LandNum"]),
                        NodeName = objReader["NodeName"].ToString(),
                        X = Convert.ToInt32(objReader["X"]),
                        Y = Convert.ToInt32(objReader["Y"]),
                        ID = Convert.ToInt32(objReader["ID"])
                    };
                }
                objReader.Close();
                return objLandNode;
            }
            catch (Exception ex)
            {                
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 根据地标号Id获取地标信息
        /// </summary>
        /// <param name="landNum">地标号</param>
        /// <param name="id">工程ID</param>
        /// <returns></returns>
        public LandNode GetLandNodeByLandId(string landId)
        {
            string sql = "Select LandId,LandNum,NodeName,NodeTypeId,X,Y,ID from LandInfo where LandId ="+landId;
            LandNode objLandNode = null;
            try
            {
                SqlDataReader objReader = SQLHelper.GetReader(sql);
                if (objReader.Read())
                {
                    objLandNode = new LandNode()
                    {
                        LandId = Convert.ToInt32(objReader["LandId"]),
                        LandNum = Convert.ToInt32(objReader["LandNum"]),
                        NodeName = objReader["NodeName"].ToString(),
                        X = Convert.ToInt32(objReader["X"]),
                        Y = Convert.ToInt32(objReader["Y"]),
                        ID = Convert.ToInt32(objReader["ID"])
                    };
                }
                objReader.Close();
                return objLandNode;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 查找是否已存在该地标
        /// </summary>
        /// <param name="landNum"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int IsExistsNodes(string landNum, string id)
        {
            string sql = "select count(*) from LandInfo where LandNum = {0} and ID = {1}";
            sql = string.Format(sql,landNum,id);
            return Convert.ToInt32(SQLHelper.GetSingalResult(sql));
        }
        #endregion

        #region 增加
        /// <summary>
        /// 批量增加节点信息
        /// </summary>
        /// <param name="listNodes"></param>
        /// <returns></returns>
        public bool AddNodes(List<LandNode> listNodes)
        {
            List<string> sqlList = new List<string>();
            foreach (LandNode item in listNodes)
            {
                string sql = "insert into  LandInfo(LandNum,NodeName,NodeTypeId,X,Y,ID) values({0},'{1}',{2},{3},{4},{5})";
                sql = string.Format(sql,item.LandNum,item.NodeName,item.NodeTypeId,item.X,item.Y,item.ID);
                sqlList.Add(sql);
            }
            try
            {
                return SQLHelper.UpdateByTran(sqlList);
            }
            catch (Exception ex)
            {               
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 增加一个地标信息
        /// </summary>
        /// <param name="objLandNode"></param>
        /// <returns>返回自增ID</returns>
        public int AddOneNodes(LandNode objLandNode)
        {
            string sql = "insert into LandInfo(LandNum,NodeName,NodeTypeId,X,Y,ID) values({0},'{1}',{2},{3},{4},{5});select @@Identity";
            sql = string.Format(sql, objLandNode.LandNum, objLandNode.NodeName, objLandNode.NodeTypeId, objLandNode.X, objLandNode.Y, objLandNode.ID);
            return Convert.ToInt32(SQLHelper.GetSingalResult(sql));
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除地标信息
        /// </summary>
        /// <param name="listNodes"></param>
        /// <returns></returns>
        public bool DeleteNodes(List<LandNode> listNodes)
        {
            List<string> sqlList = new List<string>();
            foreach (LandNode item in listNodes)
            {
                string sql = "delete from LandInfo where LandNum = {0} and ID = {1}";
                sql = string.Format(sql, item.LandNum,item.ID);
                sqlList.Add(sql);
            }
            try
            {
                return SQLHelper.UpdateByTran(sqlList);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteNodes(string id)
        {
            string sql = "delete from LandInfo where  1 = 1 ";
            if (id.Trim().Length != 0)
            {
                sql += "and ID = " + id;
            }
            return SQLHelper.Update(sql);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改地标信息
        /// </summary>
        /// <param name="listNodes"></param>
        /// <returns></returns>
        public bool UpdateNodes(List<LandNode> listNodes)
        {
            List<string> sqlList = new List<string>();
            foreach (LandNode item in listNodes)
            {
                string sql = "update LandInfo set LandNum = {0},NodeName='{1}',NodeTypeId={2},X={3},Y={4},ID ={5} where LandId = {6}";
                sql = string.Format(sql, item.LandNum, item.NodeName, item.NodeTypeId, item.X, item.Y, item.ID,item.LandId);
                sqlList.Add(sql);
            }
            try
            {
                return SQLHelper.UpdateByTran(sqlList);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="objLandNode"></param>
        /// <returns></returns>
        public int UpdateNodes(LandNode objLandNode)
        {
            string sql = "update LandInfo set LandNum = {0},NodeName='{1}',NodeTypeId={2},X={3},Y={4},ID ={5} where LandId = {6}";
            sql = string.Format(sql, objLandNode.LandNum, objLandNode.NodeName, objLandNode.NodeTypeId, objLandNode.X, objLandNode.Y, objLandNode.ID, objLandNode.LandId);
            return SQLHelper.Update(sql);
        }
        #endregion

    }
}
